# Copyright (c) 2022, hzmes and contributors
# For license information, please see license.txt

import frappe
from frappe.model.document import Document

class SRMAPReconciliation(Document):
	def before_insert(self):
		self.supplier_name = frappe.db.get_value('SRM Supplier', self.supplier_number, 'supplier_name')


	def before_save(self):
		self.validate_total()


	def validate_total(self):
		# 初始化total为0
		total_outitems = 0
		total_returnitems = 0
		total_expenseitems = 0
		if self.outitems:
			for item in self.outitems:
				total_outitems += item.cur_check_amount

		if self.returnitems:
			for item in self.returnitems:
				total_returnitems += item.cur_check_amount

		if self.expenseitems:
			for item in self.expenseitems:
				total_expenseitems += item.cur_check_amount

		self.total_outitems = total_outitems
		self.total_returnitems = total_returnitems
		self.total_expenseitems = total_expenseitems
		# 计算本次对账金额
		self.current_ap = self.total_outitems - self.total_returnitems - self.total_expenseitems

		# 计算总余额
		self.current_ap_balance = self.current_ap + self.last_ap_balance


	@frappe.whitelist()
	def get_ap_reconciliation(self):
		# 查询出货明细的组合条件
		conditions_chuhuo = f"d2.is_reconciliation = 0 AND d1.srm_supplier = '{self.supplier_number}' AND d1.creation >= '{self.date_from}' AND d1.creation <= '{self.date_to}' "
		# 出货明细result
		r_chuhuo = self.get_data_chuhuo(conditions_chuhuo)
		# 查询库存退料的组合条件
		conditions_kucuntuiliao = f"d2.is_reconciliation = 0 AND d1.return_type = '库存退料' AND d1.srm_supplier = '{self.supplier_number}' AND d1.creation >= '{self.date_from}' AND d1.creation <= '{self.date_to}' "
		# 库存退料result
		r_kucuntuiliao = self.get_data_kucuntuiliao(conditions_kucuntuiliao)
		# 查询费用明细的组合条件
		conditions_expense = f"d2.is_reconciliation = 0 AND d1.srm_supplier = '{self.supplier_number}' AND d1.creation >= '{self.date_from}' AND d1.creation <= '{self.date_to}' "
		# 费用明细result
		r_expense = self.get_data_expense(conditions_expense)

		self.outitems = []
		self.returnitems = []
		self.expenseitems = []

		# 遍历出货明细result && append [outitems]
		if r_chuhuo:
			for r in r_chuhuo:
				# print(r)
				self.append("outitems", r)

		# 遍历库存退料result && append [returnitems]
		if r_kucuntuiliao:
			for r in r_kucuntuiliao:
				# print(r)
				self.append("returnitems", r)

		# 遍历费用明细result && append [expenseitems]
		if r_expense:
			for r in r_expense:
				# print(r)
				self.append("expenseitems", r)

		self.save(ignore_permissions=True)
		self.reload()


	@frappe.whitelist()
	def get_data_chuhuo(self, conditions):
		sql1 = 	f"""
					SELECT 	d2.material_number,
							d2.material_name, 
							d2.material_model
							,d1.`name` as srm_purchase_delivery
							,d2.`name` as srm_purchase_delivery_sub
							,s2.parent as srm_purchase_order
							,s2.po_entry_seq as purchase_order_seq
							,d2.uom
							,d2.real_qty as delivery_qty
							,round(d2.real_qty * s2.tax_price,2) as delivery_amount
							,d2.check_out_qty as return_qty
							,round(d2.check_out_qty * s2.tax_price,2) as return_amount
							,d2.check_in_qty as instock_qty
							,round(d2.check_in_qty * s2.tax_price,2) as instock_amount
							,(d2.check_in_qty - d2.check_out_qty) as cur_check_qty
							,( round(d2.check_in_qty * s2.tax_price,2) - round(d2.check_out_qty * s2.tax_price,2) ) as cur_check_amount
					FROM `tabSRM Purchase Delivery` d1 
						JOIN `tabSRM Purchase Delivery Sub` d2 on d1.name = d2.parent 
						JOIN `tabSRM Purchase Order Sub` s2 on s2.po_entryid = d2.po_entryid 
						WHERE {conditions}
						LIMIT 1000;
				"""
		dt = frappe.db.sql(sql1, as_dict=1)
		return dt


	@frappe.whitelist()
	def get_data_kucuntuiliao(self, conditions):
		sql1 = 	f"""
					SELECT 	d2.material_number,
							d2.material_name, 
							d2.material_model
							,d1.`name` as srm_instock_return
							,d2.`name` as srm_instock_return_sub
							,s2.parent as srm_purchase_order
							,s2.po_entry_seq as purchase_order_seq
							,d2.uom
							,d2.receive_qty as return_qty
							,round(d2.receive_qty * s2.tax_price,2) as return_amount
							,d2.receive_qty as cur_check_qty
							,round(d2.receive_qty * s2.tax_price,2) as cur_check_amount
					FROM `tabSRM Instock Return` d1 
						JOIN `tabSRM Instock Return Sub` d2 on d1.name = d2.parent 
						JOIN `tabSRM Purchase Order Sub` s2 on s2.po_entryid = d2.po_entryid 
						WHERE {conditions}
						LIMIT 1000;
				"""
		dt = frappe.db.sql(sql1, as_dict=1)
		return dt


	@frappe.whitelist()
	def get_data_expense(self, conditions):
		sql1 = 	f"""
					SELECT 	d1.`name` as expense_billno
							, d2.expense_type
							, d2.expense_item
							, d2.amount
							, d2.amount as cur_check_amount
					FROM `tabSRM Supplier Expense` d1 
						JOIN `tabSRM Supplier Expense Sub` d2 on d1.name = d2.parent 
						WHERE {conditions}
						LIMIT 100;
				"""
		dt = frappe.db.sql(sql1, as_dict=1)
		return dt


# srm.srm.doctype.srm_ap_reconciliation.srm_ap_reconciliation.del_outitems
@frappe.whitelist()
def del_outitems(doc_name, row_name):
	doc = frappe.get_doc('SRM AP Reconciliation', doc_name)
	for item in list(doc.outitems):
		if item.name == row_name:
			doc.outitems.remove(item)
	doc.save(ignore_permissions=True)
	return doc_name


@frappe.whitelist()
def del_returnitems(doc_name, row_name):
	doc = frappe.get_doc('SRM AP Reconciliation', doc_name)
	for item in list(doc.returnitems):
		if item.name == row_name:
			doc.returnitems.remove(item)
	doc.save(ignore_permissions=True)
	return doc_name


@frappe.whitelist()
def del_expenseitems(doc_name, row_name):
	print('/n/n/n/n/n/n/n88888888888888888/n/n/n/n/n/n/n')
	doc = frappe.get_doc('SRM AP Reconciliation', doc_name)
	for item in list(doc.expenseitems):
		if item.name == row_name:
			doc.expenseitems.remove(item)
	doc.save(ignore_permissions=True)
	return doc_name